package presale_house.service.impl;

import presale_house.entity.ClientDo;
import presale_house.req.ClientRequest;
import presale_house.res.TableDTO;
import presale_house.service.ClientService;
import presale_house.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * StudentService的实现类,实现mysql语句完成对表的操作
 */
public class ClientServiceImpl implements ClientService {
    @Override
    public TableDTO retrieveClients(ClientRequest request) {
        StringBuilder sql=new StringBuilder();
        sql.append("select * from client ");
        if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())){//判断是否为空
            sql.append(" where name like '%"+request.getSearchKey().trim()+"%' ");
        }
        //分页查询
        sql.append("order by id asc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        TableDTO returnDTO=new TableDTO();
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();
            //查询记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from client ");
            if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())){
                sql.append(" where name like '%"+request.getSearchKey().trim()+"%' ");
            }
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();
            while(rs.next()){
                int count=rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;

        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    /**
     * 添加客户
     * @param clientDo
     * @return
     */
    @Override
    public boolean add(ClientDo clientDo) {
        StringBuilder sql=new StringBuilder();
        sql.append(" insert into client(name,sex,phone,address,ideal_house,select_num) ");
        sql.append(" values(?,?,?,?,?,?) ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setString(1, clientDo.getName());
            ps.setString(2, clientDo.getSex());
            ps.setString(3, clientDo.getPhone());
            ps.setString(4, clientDo.getAddress());
            ps.setString(5, clientDo.getIdea_house());
            ps.setString(6, clientDo.getSelect_num());
            return ps.executeUpdate()==1;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    /**
     * 通过ID获取客户信息
     * @param selectedClientId
     * @return
     */
    @Override
    public ClientDo getById(int selectedClientId) {
        StringBuilder sql=new StringBuilder("select * from client where id=? ");
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        ClientDo clientDo =new ClientDo();
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setInt(1, selectedClientId);
            rs=ps.executeQuery();

            while(rs.next()){
                //处理每一条查询
                int id= rs.getInt("id");
                String name= rs.getString("name");
                String sex= rs.getString("sex");
                String phone= rs.getString("phone");
                String address=rs.getString("address");
                String idea_house= rs.getString("ideal_house");
                String setSelect_num=rs.getString("select_num");
                clientDo.setId(id);
                clientDo.setName(name);
                clientDo.setSex(sex);
                clientDo.setPhone(phone);
                clientDo.setAddress(address);
                clientDo.setIdea_house(idea_house);
                clientDo.setSelect_num(setSelect_num);
            }
            return clientDo;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    /**
     * 更新客户信息
     * @param clientDo
     * @return
     */
    @Override
    public boolean update(ClientDo clientDo) {
        StringBuilder sql=new StringBuilder();
        sql.append(" update client set name=?,sex=?,phone=?,address=?,ideal_house=?,select_num=? ");
        sql.append(" where id =? ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setString(1, clientDo.getName());
            ps.setString(2, clientDo.getSex());
            ps.setString(3, clientDo.getPhone());
            ps.setString(4, clientDo.getAddress());
            ps.setString(5, clientDo.getIdea_house());
            ps.setString(6, clientDo.getSelect_num());
            ps.setInt(7, clientDo.getId());
            return ps.executeUpdate()==1;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    /**
     * 删除客户信息
     * @param selectedClientIds
     * @return
     */
    @Override
    public boolean delete(int[] selectedClientIds) {
        StringBuilder sql=new StringBuilder();
        sql.append(" delete from client where id in ( ");
        for (int i = 0; i < selectedClientIds.length; i++) {
            if(i== selectedClientIds.length-1){
                sql.append(" ? ");
            }else{
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());

            for (int i = 0; i < selectedClientIds.length; i++) {
                //设置参数,从1开始
                ps.setInt(i+1, selectedClientIds[i]);
            }
            return ps.executeUpdate()== selectedClientIds.length;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    /**
     * 修改客户数据时,回调客户数据
     * @param rs
     * @return
     * @throws SQLException
     */
    private static Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>>data =new Vector<>();
        while(rs.next()){
            //处理每一条查询
            Vector<Object>oneRecord=new Vector<>();
            int id= rs.getInt("id");
            String name= rs.getString("name");
            String sex= rs.getString("sex");
            String phone= rs.getString("phone");
            String address= rs.getString("address");
            String ideal_house= rs.getString("ideal_house");
            String select_num= rs.getString("select_num");
            oneRecord.addElement(id);
            oneRecord.addElement(name);
            oneRecord.addElement(sex);
            oneRecord.addElement(phone);
            oneRecord.addElement(address);
            oneRecord.addElement(ideal_house);
            oneRecord.addElement(select_num);
            data.addElement(oneRecord);
        }
        return data;
    }

}
